from consql import dbCon


def searchallstudent(id,name,phone):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "select id,money,phone,name from student where 1=1"
        if id:
            sql += f" and id ={id}"
        if name:
            sql += f" and name like '%{name}%'"
        if phone:
            sql += f" and phone like '%{phone}%'"
        cur.execute(sql)
        result = cur.fetchall()
        return result
    except Exception as e:
        print(e)
        return False
    finally:
        dbCon.closeCon(con)

def addstudent(id, money, password, phone, name):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "insert into student values (%s,%s,%s,%s,%s)"
        cur.execute(sql, (id, money, password, phone, name))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def modifystudent( money, password, phone, name,sid):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "update student set money=%s,password=md5(%s),phone=%s,name=%s where id=%s"
        cur.execute(sql, (money, password, phone, name,sid))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def deletestudent(sid):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "delete from student where id=%s"
        cur.execute(sql, (sid))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def searchorder(id, cid, sid, ispay):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "select id,sid,cid,case when ispay=1 then '是' else '否' end,cost,starttime,endtime from orderpay where 1=1"
        if id:
            sql += f" and id ={id}"
        if cid:
            sql += f" and cid ={cid}"
        if sid:
            sql += f" and sid ={sid}"
        if ispay != -1 and ispay!=None:
            sql += f" and ispay ={ispay}"
        cur.execute(sql)
        result = cur.fetchall()
        return result
    except Exception as e:
        print(e)
        return False
    finally:
        dbCon.closeCon(con)

def addorder(id, sid, cid, ispay, cost, starttime, endtime):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "insert into orderpay values (%s,%s,%s,%s,%s,%s,%s)"
        cur.execute(sql, (id, sid, cid, ispay, cost, starttime, endtime))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def modifyorder(id, sid, cid, ispay, cost, starttime, endtime):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "update orderpay set sid=%s,cid=%s,ispay=%s,cost=%s,starttime=%s,endtime=%s where id=%s"
        cur.execute(sql, (sid, cid, ispay, cost, starttime, endtime,id))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def deleteorder(id):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "delete from orderpay where id=%s"
        cur.execute(sql, (id))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def searchcomputer(id, rname, ctname, damaged, isfree):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "select id,case when isfree=1 then '是' else '否' end,case when damaged=1 then '是' else '否' end,ctname,rname from computer where 1=1"
        if id:
            sql += f" and id ={id}"
        if rname:
            sql += f" and rname like '%{rname}%'"
        if ctname:
            sql += f" and ctname like '%{ctname}%'"
        if damaged!= -1 and damaged!=None:
            sql += f" and damaged ={damaged}"
        if isfree!= -1 and isfree!=None:
            sql += f" and isfree ={isfree}"
        cur.execute(sql)
        result = cur.fetchall()
        return result
    except Exception as e:
        print(e)
        return False
    finally:
        dbCon.closeCon(con)

def addcomputer(id, isfree, damaged, ctname, rname):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "insert into computer values (%s,%s,%s,%s,%s)"
        cur.execute(sql, (id, isfree, damaged, ctname, rname))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def modifycomputer(id, isfree, damaged, ctname, rname):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "update computer set isfree=%s,damaged=%s,ctname=%s,rname=%s where id=%s"
        cur.execute(sql, (isfree, damaged, ctname, rname, id))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def deletecomputer(id):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "delete from computer where id=%s"
        cur.execute(sql, (id))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def searchct(name):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "select name from computertype where 1=1"
        if name:
            sql += f" and name like '%{name}%'"
        cur.execute(sql)
        result = cur.fetchall()
        return result
    except Exception as e:
        print(e)
        return False
    finally:
        dbCon.closeCon(con)

def addct(name):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "insert into computertype values (%s)"
        cur.execute(sql, (name))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def modifyct(oldname,newname):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "update computertype set name=%s where name=%s"
        cur.execute(sql, (newname,oldname))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()

def deletect(name):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "delete from computertype where name=%s"
        cur.execute(sql, (name))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def searchroom(name,rt):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "select name,rtname from room where 1=1"
        if name:
            sql += f" and name like '%{name}%'"
        if rt:
            sql += f" and rtname like '%{rt}%'"
        cur.execute(sql)
        result = cur.fetchall()
        return result
    except Exception as e:
        print(e)
        return False
    finally:
        dbCon.closeCon(con)

def addroom(name,rt):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "insert into room values (%s,%s)"
        cur.execute(sql, (name,rt))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def modifyroom(oldname,newname,rt):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "update room set name=%s,rtname=%s where name=%s"
        cur.execute(sql, (newname,rt,oldname))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def deleteroom(name):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "delete from room where name=%s"
        cur.execute(sql, (name))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def searchrt(name):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "select name from roomtype where 1=1"
        if name:
            sql += f" and name like '%{name}%'"
        cur.execute(sql)
        result = cur.fetchall()
        return result
    except Exception as e:
        print(e)
        return False
    finally:
        dbCon.closeCon(con)

def addrt(name):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "insert into roomtype values (%s)"
        cur.execute(sql, (name))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def modifyrt(oldname,newname):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "update roomtype set name=%s where name=%s"
        cur.execute(sql, (newname,oldname))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def deletert(name):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "delete from roomtype where name=%s"
        cur.execute(sql, (name))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def searchp(ct,rt):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "select onehourprice,ctname,rtname from price where 1=1"
        if ct:
            sql += f" and ctname like '%{ct}%'"
        if rt:
            sql += f" and rtname like '%{rt}%'"
        cur.execute(sql)
        result = cur.fetchall()
        return result
    except Exception as e:
        print(e)
        return False
    finally:
        dbCon.closeCon(con)

def modifyp(onehourprice,ct,rt):
    con = None
    try:
        con = dbCon.getCon()
        cur = con.cursor()
        sql = "update price set onehourprice=%s where ctname=%s and rtname=%s"
        cur.execute(sql, (onehourprice,ct,rt))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)